package com.itheima;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.dao.StudbDao;
import com.itheima.pojo.Studb;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;
import java.util.Set;

@SpringBootTest
public class zuoyetest {
    @Autowired
    public StudbDao studbDao;

    @Test
        //修改3号学生的年龄改为23，姓名改为张三3
    void test1() {
        Studb st = new Studb();
        st.setId(3);
        st.setAge(23);
        st.setName("张三3");
        int i = studbDao.updateById(st);
        System.out.println(i);
    }

    @Test
        //查询年龄在20 ~ 25之间的学生信息
    void test2() {
        LambdaQueryWrapper<Studb> sd = new LambdaQueryWrapper<>();
        sd.ge(Studb::getAge, 20);
        sd.le(Studb::getAge, 25);
        List<Studb> studbs = studbDao.selectList(sd);
        System.out.println(studbs);
    }

    @Test
        //查询所有姓张的同学
    void test3() {
        LambdaQueryWrapper<Studb> st = new LambdaQueryWrapper<>();
        st.likeRight(Studb::getName, "张");
        List<Studb> studbs = studbDao.selectList(st);
        System.out.println(studbs);

    }

    @Test
        //查询名字里有‘花’的同学
    void test4() {
        LambdaQueryWrapper<Studb> st = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<Studb> hua = st.like(Studb::getName, "花");
        List<Studb> studbs = studbDao.selectList(hua);
        System.out.println(studbs);
    }

    @Test
        //统计所有学生平均年龄、总年龄、最小年龄、最大年龄
    void test5() {
        QueryWrapper<Studb> st = new QueryWrapper<>();
        st.select("avg(age),sum(age),min(age),max(age)");
        List<Map<String, Object>> maps = studbDao.selectMaps(st);
        for (Map<String, Object> map : maps) {
            Set<Map.Entry<String, Object>> entries = map.entrySet();
            for (Map.Entry<String, Object> entry : entries) {
                String key = entry.getKey();
                Object value = entry.getValue();
                System.out.println(key + "=" + value);
            }
        }

    }

    @Test
        //分页查询，每页4条，查询第二页，并按照年龄从大到小排序
    void test6() {
        Page<Studb> page = new Page<>(2, 4);
        LambdaQueryWrapper<Studb> st = new LambdaQueryWrapper<>();
        st.orderByDesc(Studb::getAge);
        studbDao.selectPage(page, st);
        System.out.println(page.getRecords());
    }

    @Test
        //按照班级分组，查询每个班的平均年龄
    void test7() {
        QueryWrapper<Studb> st = new QueryWrapper<>();
        st.select("avg(age)");
        st.groupBy("clazz");
        List<Map<String, Object>> maps = studbDao.selectMaps(st);
        System.out.println(maps);
    }

    @Test
        //按照班级分组，查询每个班的平均年龄，并去除平均年龄在22以下的班级
    void test8() {
        QueryWrapper<Studb> stu = new QueryWrapper<>();
        QueryWrapper<Studb> select = stu.select("avg(age),clazz");

        stu.groupBy("clazz");
        stu.having("avg(age)>=22");

        List<Map<String, Object>> maps = studbDao.selectMaps(stu);
        for (Map<String, Object> map : maps) {
            for (Map.Entry<String, Object> stringObjectEntry : map.entrySet()) {
                String key = stringObjectEntry.getKey();
                Object value = stringObjectEntry.getValue();
                System.out.println(key + "=" + value);
            }
        }
    }

    @Test
        //按照班级分组，查询每个班的平均年龄，并去除平均年龄在22以下的班级。按照平均年龄从大到小排序
    void test9() {
        QueryWrapper<Studb> stu = new QueryWrapper<>();
        QueryWrapper<Studb> select = stu.select("avg(age),clazz");

        stu.groupBy("clazz");
        stu.having("avg(age)>=22");
        stu.orderByDesc("avg(age)");
        List<Map<String, Object>> maps = studbDao.selectMaps(stu);
        for (Map<String, Object> map : maps) {
            for (Map.Entry<String, Object> stringObjectEntry : map.entrySet()) {
                String key = stringObjectEntry.getKey();
                Object value = stringObjectEntry.getValue();
                System.out.println(key + "=" + value);
            }
        }
    }

    @Test
        //删除1号学生。通过deleted标识控制删除
    void test10() {
        int i = studbDao.deleteById(1);
        System.out.println(i);
    }
}
